package com.openwater.network;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;

/**
 * A database store download information 
 * @author fuxp
 * 
 */
public class DDBHelper {

    private final static int VSERSION = 1;
    private final static String DB_FILE = "download.db";
    private final static String DB_PATH = "/data/data/com.openwater/databases/";

    private final static String TAG = DDBHelper.class.getName();
    private final static int mNewVersion = VSERSION;

    static {
        final File file = new File(DB_PATH);
        if (!file.exists()) {
            file.mkdirs();
        }
        final SQLiteDatabase db = createOrOpenDatabase();
        if (db != null) {
            final int version = db.getVersion();
            if (version != mNewVersion) {
                if (db.isReadOnly()) {
                    throw new SQLiteException(
                            "Can't upgrade read-only database from version "
                                    + db.getVersion() + " to " + mNewVersion
                                    + ": " + DB_FILE);
                }
                db.beginTransaction();
                try {
                    if (version == 0) {
                        initial(db);
                    } else {
                        if (version > mNewVersion) {
                            downGrade(db, version, mNewVersion);
                        } else {
                            upGrade(db, version, mNewVersion);
                        }
                    }
                    if (!db.isOpen()) {
                        throw new IllegalStateException(
                                "call initialized ,downGrade or upGrade mothed DO NOT close the DB");
                    }
                    db.setVersion(mNewVersion);
                    db.setTransactionSuccessful();
                } finally {
                    db.endTransaction();
                }
            }
            closeDatabase(db);
        }
    }

    private static synchronized SQLiteDatabase createOrOpenDatabase() {
        try {
            SQLiteDatabase db = SQLiteDatabase.openDatabase(DB_PATH + DB_FILE,
                    null, SQLiteDatabase.OPEN_READWRITE
                            | SQLiteDatabase.CREATE_IF_NECESSARY);
            return db;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    private static synchronized void closeDatabase(SQLiteDatabase db) {
        try {
        } finally {
            if (db != null && db.isOpen()) {
                db.close();
            }
        }
    }

    /**
     * down-grade database
     * 
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    private static void downGrade(SQLiteDatabase db, int oldVersion,
            int newVersion) {

    }

    /**
     * up-grade database
     * 
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    private static void upGrade(SQLiteDatabase db, int oldVersion,
            int newVersion) {
        Log.d(TAG, "database is upgrade version from " + oldVersion + " to "
                + newVersion);
        db.execSQL("drop table if exists down_load_file;");
        db.execSQL("drop table if exists down_load_table;");
        initial(db);
    }

    /**
     * initial database
     * 
     * @param db
     */
    private static void initial(SQLiteDatabase db) {
        // Enable foreign key constraints
        db.execSQL("PRAGMA foreign_keys=ON;");
        db.execSQL("create table if not exists down_load_table ("
                + "id integer primary key autoincrement,file_name text,"
                + "store_path text,timestamp varchar(20),url text,status integer);");
        db.execSQL("create table if not exists down_load_file ("
                + "id interger ,thread_id integer,down_length integer ,"
                + "foreign key(id) references down_load_table(id));");
        Log.d(TAG, "db initialized");
    }

    /**
     * new file download task & insert into db
     * 
     * @param fileName
     * @param path
     * @param timestamp
     * @param url
     * @param status
     *            0 complete ,1 not-complete
     */
    public synchronized static void addDownloadFile(String fileName,
            String path, String timestamp, String url, int status) {
        final SQLiteDatabase db = createOrOpenDatabase();
        if (db != null) {
            final String sql = "insert into down_load_table(id, file_name,"
                    + " store_path, timestamp, url, status) values(null,?,?,?,?,?);";
            db.execSQL(sql, new String[] { fileName, path, timestamp, url,
                    String.valueOf(status) });
            Log.d(TAG, "add addDownloadFile : " + db.getPath());
        }
        closeDatabase(db);
    }

    public static synchronized boolean exists(String url) {
        final SQLiteDatabase db = createOrOpenDatabase();
        boolean flag = false;
        if (db != null) {
            final String sql = "select file_name from down_load_table where url = ?;";
            final Cursor cursor = db.rawQuery(sql, new String[] { url });
            if (cursor != null && cursor.moveToFirst()) {
                flag = true;
            }
            cursor.close();
        }
        closeDatabase(db);
        return flag;
    }

    /**
     * 
     * @param url
     * @param status
     */
    public synchronized static void updateDownloadFile(String url, int status) {
        final SQLiteDatabase db = createOrOpenDatabase();
        if (db != null) {
            final String sql = "update down_load_table set status = ? where url = ?;";
            db.execSQL(sql, new String[] { String.valueOf(status), url });
        }
        closeDatabase(db);
    }

    /**
     * 
     * @param url
     */
    public synchronized static void deleteMultithreadsDownloadInfor(String url) {
        final SQLiteDatabase db = createOrOpenDatabase();
        if (db != null) {
            final String sql = "delete from down_load_file where down_load_file.id = "
                    + "(select down_load_table.id from down_load_table where file_name = ?);";
            db.execSQL(sql, new String[] { url });
        }
        closeDatabase(db);
    }

    /**
     * select download file information order by time DESC by status
     * 
     * @param status
     * @return
     */
    public static List<String[]> getDownloadFile(int status) {
        final List<String[]> ret = new ArrayList<String[]>();
        final SQLiteDatabase db = createOrOpenDatabase();
        if (db != null) {
            final String sql = "select id,file_name,store_path,timestamp,url from "
                    + "down_load_table where status = ? order by timestamp DESC;";
            final Cursor cursor = db.rawQuery(sql,
                    new String[] { String.valueOf(status) });
            if (cursor != null) {
                try {
                    while (cursor.moveToNext()) {
                        final String[] column = new String[cursor
                                .getColumnCount()];
                        for (int i = 0; i < column.length; ++i) {
                            column[i] = cursor.getString(i);
                        }
                        ret.add(column);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    cursor.close();
                }
            }
        }
        closeDatabase(db);
        return ret;
    }

    /**
     * 
     * @param url
     * @return
     */
    public static synchronized Map<Integer, Integer> getMultithreadsDownloadInfor(
            String url) {
        final Map<Integer, Integer> retMap = new HashMap<Integer, Integer>();
        final SQLiteDatabase db = createOrOpenDatabase();
        if (db != null) {
            final String sql = "select thread_id ,down_length from down_load_file where down_load_file.id = "
                    + "( select down_load_table.id from down_load_table where url = ?);";
            Log.d(TAG, "sql:" + sql);
            Cursor cursor = db.rawQuery(sql, new String[] { url });
            if (cursor != null) {
                try {
                    while (cursor.moveToNext()) {
                        String id = cursor.getString(0);
                        String length = cursor.getString(1);
                        retMap.put(Integer.parseInt(id),
                                Integer.parseInt(length));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    cursor.close();
                }
            }
        }
        closeDatabase(db);
        return retMap;
    }

    public synchronized static void updateMultithreadsDownloadInfor(String url,
            Map<Integer, Integer> map) {
        final SQLiteDatabase db = createOrOpenDatabase();
        if (db != null) {
            try {
                final Set<Entry<Integer, Integer>> set = map.entrySet();
                final Iterator<Entry<Integer, Integer>> iter = set.iterator();
                while (iter.hasNext()) {
                    final Entry<Integer, Integer> entery = iter.next();
                    final String sql = "update down_load_file set down_length = ?"
                            + " where thread_id = ? and down_load_file.id ="
                            + " (select down_load_table.id from down_load_table where url =?);";
                    Log.d(TAG, "sql : " + sql);
                    db.execSQL(sql,
                            new String[] { String.valueOf(entery.getKey()),
                                    String.valueOf(entery.getValue()), url });
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        closeDatabase(db);
    }

    public synchronized static void updateMultithreadsDownloadInfor(String url,
            int threadId, int downLength) {
        final SQLiteDatabase db = createOrOpenDatabase();
        if (db != null) {
            final String sql = "update down_load_file set down_length = ? where thread_id = ? and down_load_file.id ="
                    + " (select down_load_table.id from down_load_table where url =?);";
            Log.d(TAG, "sql : " + sql);
            db.execSQL(
                    sql,
                    new String[] { String.valueOf(downLength),
                            String.valueOf(threadId), url });
        }
        closeDatabase(db);
    }

}
